package com.dao;

import com.entity.Experiment;
import com.util.DBpool.DBconnection;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;

import static com.util.GlobalArgs.*;

/**
 * Created by Dell on 2018/4/13.
 */
public class ExperimentDao {
    /**
     * 将Experiment数据存储到数据库中
     * @param experiment
     */
    public static boolean data_save(Experiment experiment){
        DBconnection saveInDB = new DBconnection();
        saveInDB.setSheet_name("experiment_User_AND_Method");
        if (!saveInDB.saveDataToDB(experiment.getExperiment_User_AND_Method())) {
            System.out.println("Experiment_User_AND_Method上传错误");
            return false;
        }
        saveInDB.setSheet_name("experiment_RawMaterial");
        if(!saveInDB.saveDataToDB(experiment.getExperiment_RawMaterial())) {
            System.out.println("Experiment_RawMaterial上传错误");
            return false;
        }
        saveInDB.setSheet_name("experiment_Characterization");
        if(!saveInDB.saveDataToDB(experiment.getExperiment_Characterization())) {
            System.out.println("Experiment_Characterization上传错误");
            return false;
        }
        saveInDB.setSheet_name("experiment_Biological_evaluation");
        if(!saveInDB.saveDataToDB(experiment.getExperiment_Biological_evaluation())) {
            System.out.println("Experiment_Biological_evaluation上传错误");
            return false;
        }
        return true;
    }

    /**
     * 获取已有模板实验原料的名称
     * @return
     */
    public static HashMap<String,String> materialAcquisition(){
        HashMap<String,String> experimentMaterial = new HashMap<>();
        ArrayList<String> materialList = new ArrayList<>();
        DBconnection connection = new DBconnection();

        connection.setSheet_name("template_experimentalMaterial_bioactiveGlass");
        materialList = connection.getDataFromFiled(0);
        experimentMaterial.put("bioactiveGlass", StringUtils.join(materialList,"wlcniubi"));
        materialList.clear();
        connection.setSheet_name("template_experimentalMaterial_calciumPhosphate");
        materialList = connection.getDataFromFiled(0);
        experimentMaterial.put("calciumPhosphate", StringUtils.join(materialList,"wlcniubi"));
        materialList.clear();
        connection.setSheet_name("template_experimentalMaterial_calciumSilicate");
        materialList = connection.getDataFromFiled(0);
        experimentMaterial.put("calciumSilicate", StringUtils.join(materialList,"wlcniubi"));
        materialList.clear();
        connection.setSheet_name("template_experimentalMaterial_calciumSulfate");
        materialList = connection.getDataFromFiled(0);
        experimentMaterial.put("calciumSulfate", StringUtils.join(materialList,"wlcniubi"));
        materialList.clear();
        connection.setSheet_name("template_experimentalMaterial_copperSulphide");
        materialList = connection.getDataFromFiled(0);
        experimentMaterial.put("copperSulphide", StringUtils.join(materialList,"wlcniubi"));
        materialList.clear();
        connection.setSheet_name("template_experimentalMaterial_magnesiumSilicide");
        materialList = connection.getDataFromFiled(0);
        experimentMaterial.put("magnesiumSilicide", StringUtils.join(materialList,"wlcniubi"));
        materialList.clear();
        connection.setSheet_name("template_experimentalMaterial_nanoCluster");
        materialList = connection.getDataFromFiled(0);
        experimentMaterial.put("nanoCluster", StringUtils.join(materialList,"wlcniubi"));
        materialList.clear();
        connection.setSheet_name("template_experimentalMaterial_selenium");
        materialList = connection.getDataFromFiled(0);
        experimentMaterial.put("selenium", StringUtils.join(materialList,"wlcniubi"));
        materialList.clear();
        connection.setSheet_name("template_experimentalMaterial_titaniumDioxide");
        materialList = connection.getDataFromFiled(0);
        experimentMaterial.put("titaniumDioxide", StringUtils.join(materialList,"wlcniubi"));
        materialList.clear();

        return experimentMaterial;
    }

    /**
     * 获取指定模板实验原料内容
     * @param tableId
     * @param index
     * @return
     */
    public static ArrayList<String> materialContentAcquisition(String tableId,String index) {
        String tableName = "template_experimentalMaterial_" + tableId;
        DBconnection connection = new DBconnection();

        connection.setSheet_name(tableName);
        ArrayList<ArrayList<String>> materials = connection.getDataFromDB();
        return materials.get(Integer.parseInt(index));
    }

    /**
     * 根据关键词搜索数据库中的实验数据
     * @param base
     * @param keyword
     * @return
     */
    public static ArrayList<ArrayList<String>> searchExperiment_data_by_keyword(String base,String keyword) {
        ArrayList<String> table_names = new ArrayList<String>() {{
            add("experiment_User_AND_Method");
            add("experiment_RawMaterial");
            add("experiment_Characterization");
            add("experiment_Biological_evaluation");
        }};
        DBconnection saveInDB = new DBconnection();

        ArrayList<ArrayList<String>> search_result = getID_by_keyword(saveInDB, base, keyword); //获得关键字查询到的id
        if(search_result.size() > 0) {
            for (String temp_table_name: table_names){

                saveInDB.setSheet_name(temp_table_name);
                ArrayList<ArrayList<String>> Sheet_data = saveInDB.getDataFromDB();

                for(int i = 0; i < search_result.size(); i++) {
                    for(ArrayList<String> row_in_Sheet: Sheet_data) {
                        if(search_result.get(i).get(0).equals(row_in_Sheet.get(0))) {
                            row_in_Sheet.remove(0);

                            ArrayList<String> temp_row_data = new ArrayList<>();
                            temp_row_data.addAll(search_result.get(i));
                            temp_row_data.addAll(row_in_Sheet);

                            search_result.set(i, temp_row_data);
                            break;
                        }
                    }
                }
            }
        }
        return search_result;
    }

    /**
     *根据关键字查找id
     * @param saveInDB
     * @param base
     * @param keyword
     * @return
     */
    private static ArrayList<ArrayList<String>> getID_by_keyword(DBconnection saveInDB,String base,String keyword){
        String table_name = base_index_experiment.get(base).split("-")[0];
        int col_index = Integer.parseInt(base_index_experiment.get(base).split("-")[1]);
        ArrayList<ArrayList<String>> id_arr = new ArrayList<>();

        saveInDB.setSheet_name(table_name);
        ArrayList<ArrayList<String>> temp_data = saveInDB.getDataFromDB();
        for (ArrayList<String> row_content : temp_data){
            if (keyword.contains(row_content.get(col_index))|| row_content.get(col_index).contains(keyword)){
                ArrayList<String> temp_id = new ArrayList<>();
                temp_id.add(row_content.get(0));
                id_arr.add(temp_id);
            }
        }
        return id_arr;
    }


    /**
     * 获取实验数据表标题
     * @return
     */
    public static ArrayList<String> getTitle() {
        ArrayList<String> table_names = new ArrayList<String>(){{
            add("experiment_User_AND_Method");
            add("experiment_RawMaterial");
            add("experiment_Characterization");
            add("experiment_Biological_evaluation");
        }};

        DBconnection saveInDB = new DBconnection();;
        ArrayList<String> title = new ArrayList<>();
        for(int i = 0; i < table_names.size(); i++) {

            saveInDB.setSheet_name(table_names.get(i));
            ArrayList<String> temp = saveInDB.getTitleFromDB();
            if(i != 0) {
                temp.remove(0);
            }
            title.addAll(temp);
        }
        System.out.println("title size ;;;;;;" + title.size());
        return title;
    }


}
